CREATE SCHEMA [Crm] AUTHORIZATION [dbo]
GO

CREATE SCHEMA [Meta] AUTHORIZATION [dbo]
GO

CREATE TABLE [Meta].[References](
[id_reference] uniqueidentifier NOT NULL ROWGUIDCOL,
[last_update] datetime null,
[Google] nvarchar(32) NULL,
[Google_last_update] datetime null,
[ZohoCRM] int NULL,
[ZohoCRM_last_update] datetime null,
[from] nvarchar(50) NULL,
[Validated] bit NOT NULL
);
GO
ALTER TABLE [Meta].[References] ADD CONSTRAINT [PK_Meta_References] PRIMARY KEY ([id_reference]);
GO
CREATE INDEX [IX_References_Google] ON [Meta].[References] ([Google] ASC);
GO
CREATE INDEX [IX_References_Zoho] ON [Meta].[References] ([ZohoCRM] ASC);
GO

CREATE TABLE [Meta].[Search](
[id_reference] uniqueidentifier NOT NULL ROWGUIDCOL,
[display_name] nvarchar(50) NOT NULL,
[item_type] varchar(30) NOT NULL,
[search_field] nvarchar(450) NOT NULL,
[reverse_search_field] nvarchar(450) NOT NULL
);
GO
ALTER TABLE [Meta].[Search] ADD CONSTRAINT [PK_Meta_Search] PRIMARY KEY ([id_reference]);
GO
CREATE INDEX [IX_Search_Search] ON [Meta].[Search] ([search_field] ASC);
GO
CREATE INDEX [IX_Search_Reverse] ON [Meta].[Search] ([reverse_search_field] ASC);
GO

CREATE TABLE [Meta].[Geolocalisation](
[id_reference] uniqueidentifier NOT NULL ROWGUIDCOL,
[display_name] nvarchar(50) NOT NULL,
[item_type] varchar(30) NOT NULL,
[point] geography not null
);
ALTER TABLE [Meta].[Geolocalisation] ADD CONSTRAINT [PK_Meta_Geolocalisation] PRIMARY KEY ([id_reference]);
GO

CREATE SPATIAL INDEX IX_Geolocalisation
   ON [Meta].[Geolocalisation]([point]);
GO
CREATE TABLE [Crm].[Entity](
[id_Entity] uniqueidentifier NOT NULL ROWGUIDCOL,
[manager] nvarchar(100) null,
[surname] nvarchar(100) not null,
[name] nvarchar(100) not null,
[company] nvarchar(100) null,
[title] nvarchar(20) null,
[phone] varchar(15) null,
[mobile] varchar(15) null,
[source] nvarchar(50) null,
[url] nvarchar(50) null,
[mail] nvarchar(30) null,
[fax] varchar(15) null,
[status] nvarchar(20) null,
[skype] nvarchar(30) null,
[twitter] nvarchar(50) null,
[sector] nvarchar(50) null,
[no_mail] bit not null,
[address] nvarchar(200) null,
[zip_code] varchar(10) null,
[city] nvarchar(50) null,
[country] nvarchar(50) null,
[description] nvarchar(500) null
);

ALTER TABLE [Crm].[Entity] ADD CONSTRAINT [PK_CRM_Entity] PRIMARY KEY ([id_entity]);
GO
ALTER TABLE [Crm].[Entity] ADD CONSTRAINT [FK_Entity_Reference] FOREIGN KEY ([id_entity]) REFERENCES [Meta].[References]([id_reference]) ON DELETE NO ACTION ON UPDATE NO ACTION;